﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Intelligent.OTC.Common.UnitOfWork;
using Intelligent.OTC.Domain.DataModel;
using Intelligent.OTC.Domain.Repositories;
using System.Configuration;
using System.IO;
using Intelligent.OTC.Domain;
using Intelligent.OTC.Common;
using System.Data.Entity.Validation;
using Intelligent.OTC.Common.Exceptions;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using Intelligent.OTC.Common.Utils;
using Microsoft.Office.Interop.Excel;

namespace Intelligent.OTC.Business
{
    public class InvoiceService
    {
        public List<InvoiceAging> lstPFE;
        public List<InvoiceAging> lstPCN;
        public List<InvoiceAging> lstADHK;
        public const string paxarUs = "Paxar - US";
        public const string paxarCn = "Paxar - CN";
        public const string strSoaPathKey = "GenerateSOAPath";//GenerateSOA路径的config保存名
        public const string strTemplatePathKey = "TemplateSOAPath";//TemplateSOA路径的config保存名
        public InvoiceService()
        { 
        }

        public OTCRepository CommonRep { get; set; }

        #region 
        public IList<InvoiceAging> invoiceInfoGet()
        {
            return CommonRep.GetQueryable<InvoiceAging>().ToList();
        }
        #endregion

        public List<InvoiceAging> invoiceInfoGetByNum(string cusnum)
        {
            return CommonRep.GetQueryable<InvoiceAging>()
                .Where(m => m.CustomerNum == cusnum && m.Deal == AppContext.Current.User.Deal).ToList();
        }


        #region Set content in SOA template
        //public List<string> setContent(List<InvoiceAging> invoicelist,List<string> intids)
        public List<string> setContent(List<int> intids, int Type, out List<SOAInfoReportItem> soaReports)
        {
            soaReports = new List<SOAInfoReportItem>();
            SOAInfoReportItem soaReport = new SOAInfoReportItem();
            DateTime now =AppContext.Current.User.Now.Date;
            //lstPFE = new List<InvoiceAging>();
            //lstPCN = new List<InvoiceAging>();
            //lstADHK = new List<InvoiceAging>();
            var TypeTem = "";
            if (Type == 1) {
                TypeTem = "SOA";
            }
            else if (Type == 2) {
                TypeTem = "2nd Reminder";
            }
            else if (Type == 3) {
                TypeTem = "Final Reminder";
            }
            List<InvoiceAging> invoicelist = new List<InvoiceAging>();
            invoicelist = (from inv in CommonRep.GetQueryable<InvoiceAging>()
                           where intids.Contains(inv.Id)
                           select inv).ToList();

            //生成的文件路径的列表
            List<string> lstReportPath = new List<string>();

            //获取对应的site信息
            List<Sites> lstSites = CommonRep.GetDbSet<Sites>().ToList();

            try {
                // 判断获取的结果是否为空
                if (invoicelist == null || invoicelist.Count <= 0)
                {
                    throw new OTCServiceException("There are no existing datas, please refresh.");
                }

                foreach (var item in intids)
                {
                    if (invoicelist.Find(m => m.Id == item) == null)
                    {
                        throw new OTCServiceException("There are no existing datas, please refresh.");
                    }
                }
                // 结果不为空，分配SOA
                //foreach (var res in invoicelist)
                //{
                //    if (res.LegalEntity == paxarUs)
                //    {
                //        lstPFE.Add(res);
                //    }
                //    else if (res.LegalEntity == paxarCn)
                //    {
                //        lstPCN.Add(res);
                //    }
                //    else 
                //    {
                //        lstADHK.Add(res);
                //    }
                //}

                IEnumerable<IGrouping<string, InvoiceAging>> query = invoicelist.GroupBy(m => m.CustomerNum);
                foreach (IGrouping<string, InvoiceAging> info in query)
                {
                    
                    //This is group by customernum
                    List<InvoiceAging> CusGroupList = info.ToList<InvoiceAging>();

                    //=========added by alex body中显示附件名+Currency=========
                   // List<MyCurClass> lstCurAmt = new List<MyCurClass>();
                    List<MyCurClass> lstCurAmtOverDue = new List<MyCurClass>();
                    List<MyCurClass> lstCurAmtCurrent = new List<MyCurClass>();

                    IEnumerable<IGrouping<string, InvoiceAging>> queryl = CusGroupList.GroupBy(m => m.LegalEntity);
                    foreach (IGrouping<string, InvoiceAging> infol in queryl)
                    {
                        soaReport = new SOAInfoReportItem();
                        //This is group by LegalEntity
                        List<InvoiceAging> LegalGroupList = infol.ToList<InvoiceAging>();

                        //=========added by alex body中显示附件名+Currency=====================
                        //balance 分币种加和
                        //lstCurAmt = LegalGroupList.GroupBy(a => a.Currency).Select(o => new MyCurClass { cur = o.Key, amt = o.Sum(item => item.BalanceAmt) }).ToList();
                        //=============================================added by zhangYu start
                        lstCurAmtOverDue = LegalGroupList.Where(o => o.DueDate < now).GroupBy(a => a.Currency).Select(o => new MyCurClass { cur = o.Key, amt = o.Sum(item => item.BalanceAmt) }).ToList();
                        lstCurAmtCurrent = LegalGroupList.Where(o => o.DueDate >= now).GroupBy(a => a.Currency).Select(o => new MyCurClass { cur = o.Key, amt = o.Sum(item => item.BalanceAmt) }).ToList();
                        //string cutAmt = "";
                        soaReport.overdueCharge = new List<Tuple<string, decimal>>();
                        lstCurAmtOverDue.ForEach(o =>
                        {
                            //cutAmt += o.cur + ":" + Math.Round(o.amt.Value, 2).ToString() + " ";
                            //=============================================added by zhangYu start========================//
                            Tuple<string, decimal> overdueCharge = new Tuple<string, decimal>(o.cur, Math.Round(o.amt.Value, 2));
                            soaReport.overdueCharge.Add(overdueCharge);
                        });

                        lstCurAmtCurrent.ForEach(o =>
                        {
                           Tuple<string, decimal> currentCharge = new Tuple<string, decimal>(o.cur, Math.Round(o.amt.Value, 2));
                            soaReport.currentCharge.Add(currentCharge);
                        });
                        //===================================================added by zhangYu END========================//

                        string alia = lstSites.Find(m => m.LegalEntity == LegalGroupList[0].LegalEntity).Alia;
                        string cus = LegalGroupList[0].CustomerNum + LegalGroupList[0].CustomerName
                            .Replace("&", "and").Replace("?", "").Replace("%", "").Replace("#", "")
                            .Replace("/", "").Replace("=", "").Replace(",", "").Replace("*", "")
                            .Replace(@"\", "").Replace("<", "").Replace(">", "").Replace("|", "")
                            .Replace(":","").Replace(@"\\","");
                        //string legal = lstSites.Find(m => m.LegalEntity == LegalGroupList[0].LegalEntity).Id.ToString();

                        string soaFileName = cus + "-" + TypeTem + "-" + alia + ".xlsx";

                        //=========added by alex body中显示附件名+Currency=====================

                        //if (string.IsNullOrEmpty(attInfo))
                        //{
                        //attInfo = attInfo + soaFileName + " " + cutAmt + "<br />" + Environment.NewLine;
                       //=============================================added by zhangYu start========================//
                        soaReport.LegalEntity = LegalGroupList[0].LegalEntity;
                        soaReport.CustomerNum = LegalGroupList[0].CustomerNum;
                        soaReport.CustomerName = LegalGroupList[0].CustomerName;
                        soaReport.BillGroupCode = LegalGroupList[0].BillGroupCode;
                        soaReports.Add(soaReport); 
                      //=============================================added by zhangYu end=========================//
                        //}
                        //else
                        //{
                        //    attInfo = Environment.NewLine + attInfo + soaFileName + " " + cutAmt;
                        //}

                        //====================================================================

                        FileService fs = SpringFactory.GetObjectImpl<FileService>("FileService");
                        //模板文件  
                        string templateFile = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[strTemplatePathKey].ToString());

                        string tmpFile = Path.Combine(Path.GetTempPath(), DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx");

                        setData(templateFile, tmpFile, lstSites, LegalGroupList);

                        using(FileStream stream = File.OpenRead(tmpFile))
                        {
                            lstReportPath.Add(fs.AddAppFile(soaFileName, stream, FileType.SOA).FileId);
                        }

                        File.Delete(tmpFile);
                    }
                    
                }
                

                //// 向Excel中设置相对应的数据
                //if (lstPFE.Count > 0) 
                //{
                //    //导出文件  
                //    string ReportFileName = soaPath + @"\SOA-PFE.xlsx";
                //    string SysReportFileName = sysSoaPath + "SOA-PFE.xlsx";

                //    // Get file virtual folder
                //    string outName = getVirtualFullPath("SOA-PFE.xlsx");
                //    lstReportPath.Add(outName);

                //    //设置数据
                //    setData(TempletFileName, ReportFileName, SysReportFileName, lstSites, lstPFE);
                //}

                //if (lstPCN.Count > 0)
                //{
                //    //导出文件  
                //    string ReportFileName = soaPath + @"\SOA-PCN.xlsx";
                //    string SysReportFileName = sysSoaPath + "SOA-PCN.xlsx";

                //    // Get file virtual folder
                //    string outName = getVirtualFullPath("SOA-PCN.xlsx");
                //    lstReportPath.Add(outName);

                //    //设置数据
                //    setData(TempletFileName, ReportFileName, SysReportFileName, lstSites, lstPCN);
                //}

                //if (lstADHK.Count > 0)
                //{
                //    //导出文件  
                //    string ReportFileName = soaPath + @"\SOA-ADHK.xlsx";
                //    string SysReportFileName = sysSoaPath + "SOA-ADHK.xlsx";

                //    // Get file virtual folder
                //    string outName = getVirtualFullPath("SOA-ADHK.xlsx");
                //    lstReportPath.Add(outName);

                //    //设置数据
                //    setData(TempletFileName, ReportFileName, SysReportFileName, lstSites, lstADHK);
                //}
            }
            catch (Exception ex)
            {
                Helper.Log.Error("Failed to create SOA Excel!", ex);
                throw;
            }

            return lstReportPath;
        }

        private static string getVirtualFullPath(string fileName)
        {
            HttpRequest request = HttpContext.Current.Request;
            StringBuilder appUriBuilder = new StringBuilder(request.Url.Scheme);
            appUriBuilder.Append(Uri.SchemeDelimiter);
            appUriBuilder.Append(request.Url.Authority);
            if (String.Compare(request.ApplicationPath, @"/") != 0)
            {
                appUriBuilder.Append(request.ApplicationPath);
            }
            string outName = appUriBuilder.ToString();
            outName += (ConfigurationManager.AppSettings[strSoaPathKey].ToString().Trim('~') + fileName);
            return outName;
        }
        public string getVirtualFullPathCommon(string fileName)
        {
            HttpRequest request = HttpContext.Current.Request;
            StringBuilder appUriBuilder = new StringBuilder(request.Url.Scheme);
            appUriBuilder.Append(Uri.SchemeDelimiter);
            appUriBuilder.Append(request.Url.Authority);
            if (String.Compare(request.ApplicationPath, @"/") != 0)
            {
                appUriBuilder.Append(request.ApplicationPath);
            }
            string outName = appUriBuilder.ToString();
            outName += (fileName.Trim('~'));
            return outName;
        }
        #endregion

        #region Set SOA report Datas
        private void setData(string templateFileName, string repfileName, List<Sites> lstSites, List<InvoiceAging> lstDatas)
        {
            int rowNo = 15;
            int colNo = 7;
            string companyName = string.Empty;
            string address1 = string.Empty;
            string address2 = string.Empty;
            string telAndFax = string.Empty;
            string coName = string.Empty;
            string bankName = string.Empty;
            string bankAddress = string.Empty;
            string accountNoUsd = string.Empty;
            string accountNoHkd = string.Empty;
            string swiftCode = string.Empty;
            //decimal rate = 0;
            List<InvoiceAging> lstInvAging = new List<InvoiceAging>();

            try
            {
                lstInvAging = lstDatas.OrderBy(o => o.DueDate).ToList();
                NpoiHelper helper = new NpoiHelper(templateFileName);
                helper.Save(repfileName, true);
                helper = new NpoiHelper(repfileName);
                string sheetName = "";

                foreach (string sheet in helper.Sheets)
                {
                    sheetName = sheet;
                    break;
                }

                //设置sheet
                helper.ActiveSheetName = sheetName;

                //通过siteCode获取对应的site信息
                var sitesInfo = lstSites.Where(o => o.LegalEntity == lstInvAging[0].LegalEntity).ToList();

                //设置Excel的固定信息
                if (sitesInfo.Count > 0)
                {
                    companyName = sitesInfo[0].SiteNameSys;
                    address1 = sitesInfo[0].Address1;
                    address2 = sitesInfo[0].Address2;
                    telAndFax = "TEL:" + sitesInfo[0].Telephone + " FAX:" + sitesInfo[0].Fax;
                    coName = sitesInfo[0].SiteNameSys.ToUpper();
                    bankName = sitesInfo[0].BankName;
                    bankAddress = sitesInfo[0].BankAddress;
                    accountNoUsd = sitesInfo[0].AccountNoUsd;
                    accountNoHkd = sitesInfo[0].AccountNoHkd;
                    swiftCode = sitesInfo[0].SwiftCode;
                }

                //头部信息
                helper.SetData(4, 0, companyName);
                helper.SetData(6, 0, address1);
                helper.SetData(7, 0, address2);
                helper.SetData(8, 0, telAndFax);
                helper.SetData(2, 8, coName);
                helper.SetData(3, 8, bankName);
                helper.SetData(4, 8, bankAddress);
                helper.SetData(5, 8, accountNoUsd);
                helper.SetData(6, 8, accountNoHkd);
                helper.SetData(7, 8, swiftCode);
                helper.SetData(10, 1, lstInvAging[0].CustomerName.Replace("&", "and").Replace("?", "").Replace("%", "").Replace("#", "").Replace("/", "").Replace("=", "").Replace(",", "").Replace("*", "").Replace(@"\", "").Replace("<", "").Replace(">", "").Replace("|", "").Replace(":", "").Replace(@"\\", ""));
                helper.SetData(11, 1, lstInvAging[0].CustomerNum);
                helper.SetData(11, 7, "Please make payable to '" + coName + "' and send to");
                helper.SetData(12, 7, address1);

                //Rate
                //List<RateTran> rateList = CommonRep.GetQueryable<RateTran>()
                //    .Where(o => o.EffectiveDate <= AppContext.Current.User.Now.Date
                //        && o.ExpiredDate >= AppContext.Current.User.Now.Date).ToList();

                //设置Excel的内容信息
                foreach (var lst in lstInvAging)
                {
                    int cellRowNum = rowNo + 1;
                    string cellFormula = "IF(G" + cellRowNum + "=\"\",\"\",($B$13 - G" + cellRowNum + "))";
                    helper.SetFormula(rowNo, colNo, cellFormula, false);
                    helper.SetData(rowNo, 0, lst.CustomerNum);
                    helper.SetData(rowNo, 1, lst.CustomerName.Replace("&", "and").Replace("?", "").Replace("%", "").Replace("#", "").Replace("/", "").Replace("=", "").Replace(",", "").Replace("*", "").Replace(@"\", "").Replace("<", "").Replace(">", "").Replace("|", "").Replace(":", "").Replace(@"\\", ""));
                    helper.SetData(rowNo, 2, lst.MstCustomer);
                    helper.SetData(rowNo, 3, lst.InvoiceNum);
                    helper.SetData(rowNo, 4, lst.PoNum);
                    helper.SetData(rowNo, 5, lst.InvoiceDate.Value);
                    helper.SetData(rowNo, 6, lst.DueDate.Value);
                    //string dt =
                    ////new TimeSpan(AppContext.Current.User.Now.Ticks).Subtract(new TimeSpan(Convert.ToDateTime(lst.DueDate).Ticks)).Duration().Days.ToString();
                    //(AppContext.Current.User.Now.Date - Convert.ToDateTime(lst.DueDate).Date).Days.ToString();
                    //helper.SetData(rowNo, 7, dt);
                    helper.SetData(rowNo, 8, lst.Class);

                    //decimal? balanceAmt = 0;
                    //string currency = "USD";

                    //if (lst.Currency != "USD")
                    //{
                    //    rate = rateList.Find(m => m.ForeignCurrency == lst.Currency).Rate.Value;
                    //    balanceAmt = rate * lst.BalanceAmt;
                    //}
                    //else
                    //{
                    //    balanceAmt = lst.BalanceAmt;
                    //}

                    helper.SetData(rowNo, 9, lst.OriginalAmt);
                    helper.SetData(rowNo, 10, lst.BalanceAmt);
                    helper.SetData(rowNo, 11, lst.Currency);
                    helper.SetData(rowNo, 12, lst.SoNum);
                    helper.SetData(rowNo, 13, lst.Remark);

                    //helper.SetData(rowNo, 19, balanceAmt);
                    //helper.SetData(rowNo, 20, currency);

                    //if (lst.DueDate.Value < AppContext.Current.User.Now)
                    //{
                    //    helper.SetData(rowNo, 17, balanceAmt);
                    //    helper.SetData(rowNo, 18, "USDO");
                    //}

                    rowNo++;
                }

                //formula calcuate result
                helper.ForceFormulaRecalculation(false);
                helper.Save(repfileName, true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public List<InvoiceLog> LogInvoice(List<int> invIds, Action<InvoiceLog> supplymentCallBack)
        {
            string deal = AppContext.Current.User.Deal.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            DateTime operDT = AppContext.Current.User.Now;

            List<InvoiceAging> invList = (from inv in CommonRep.GetDbSet<InvoiceAging>()
                                          where invIds.Contains(inv.Id)
                                          select inv).ToList<InvoiceAging>();

            //.FindAll(m => m.Deal == deal);
            List<InvoiceLog> invlogList = new List<InvoiceLog>();
            InvoiceLog invlog = new InvoiceLog();
            //foreach (var cus in cusnums) {
            //    var newinvList = invList.FindAll(m => m.CustomerNum == cus);
            foreach (var inv in invList)
            {
                invlog = new InvoiceLog();
                invlog.Deal = deal;
                invlog.CustomerNum = inv.CustomerNum;
                invlog.InvoiceId = inv.InvoiceNum;
                invlog.LogDate = operDT;
                invlog.LogPerson = eid;
                invlog.LogType = "1"; //soaFlg;
                invlog.OldStatus = inv.States;
                invlog.NewStatus = inv.States;
                invlog.OldTrack = inv.TrackStates;
                invlog.Discription = "";
                supplymentCallBack(invlog);
                invlogList.Add(invlog);
            }
            //}
            //CommonRep.AddRange(invlogList);
            //CommonRep.Commit();
            //using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
            //{
            //(CommonRep.GetDBContext() as OTCEntities).BulkInsert(invlogList);
            //    scope.Complete();
            //}
            return invlogList;
        }

        public class MyCurClass
        {
            public string cur { get; set; }
            public decimal? amt { get; set; }
        }

        /// <summary>
        /// create soa info report based on the reportItemList retrieved from the setContent method. added by zhangYu
        /// </summary>
        /// <param name="reportItemList"></param>
        /// <returns></returns>
        public string GetInvoiceReport(List<SOAInfoReportItem> reportItemList)
        {
            StringBuilder htmlReport = new StringBuilder();
            Dictionary<string, decimal> overdueCurrencys = new Dictionary<string, decimal>();
            Dictionary<string, decimal> currCurrencys = new Dictionary<string, decimal>();
            Dictionary<string, decimal> overdueCurrencysTotal = new Dictionary<string, decimal>();
            Dictionary<string, decimal> currCurrencysTotal = new Dictionary<string, decimal>();
            #region ready
            foreach (SOAInfoReportItem rep in reportItemList)
            {
                //get the OverDuecharge total col 
                foreach (Tuple<string, decimal> overDue in rep.overdueCharge)
                {
                    if (!overdueCurrencys.ContainsKey(overDue.Item1))
                    {
                        overdueCurrencys.Add(overDue.Item1, overDue.Item2);
                        overdueCurrencysTotal.Add(overDue.Item1, 0);
                    }
                }
                //get the currentcharge total col
                foreach (Tuple<string, decimal> curr in rep.currentCharge)
                {
                    if (!currCurrencys.ContainsKey(curr.Item1))
                    {
                        currCurrencys.Add(curr.Item1, curr.Item2);
                        currCurrencysTotal.Add(curr.Item1, 0);
                    }
                }
            }
            #endregion
            #region header
            int colCountOverDue = overdueCurrencys.Count();
            int colCountCurrent = currCurrencys.Count();
            // table header title
            htmlReport.Append("<table cellSpacing='0' cellPadding='0' width ='100%' border='1'");
            htmlReport.Append(">");
            htmlReport.Append("<tr valign='middle' bgcolor='pink'>");
            htmlReport.Append("<th rowspan='2'>Entity</th>");
            htmlReport.Append("<th rowspan='2'>Customer Code</th>");
            htmlReport.Append("<th rowspan='2'>Customer Name</th>");
            //Overdue Charge
            if (colCountOverDue > 0)
            {
                htmlReport.Append("<th  colspan=" + colCountOverDue * 2 + "><b>Overdue</b></th>");
            }
            //Current Charge
            if (colCountCurrent > 0)
            {
                htmlReport.Append("<th  colspan=" + colCountCurrent * 2 + "><b>Current</b></th>");
            }

            htmlReport.Append("</tr>");
            htmlReport.Append("<tr valign='middle' bgcolor='pink'>");
            //Currency,Amount
            for (int i = 0; i < colCountOverDue; i++)
            {
                htmlReport.Append("<td valign='middle' align='middle'><b>Currency</b></td>");
                htmlReport.Append("<td valign='middle' align='middle'><b>Amount</b></td>");
            }
            //Currency,Amount
            for (int i = 0; i < colCountCurrent; i++)
            {
                htmlReport.Append("<td valign='middle' align='middle'><b>Currency</b></td>");
                htmlReport.Append("<td valign='middle' align='middle'><b>Amount</b></td>");
            }
            htmlReport.Append("</tr>");
            #endregion
            #region body
            Customer cus = new Customer();
            foreach (SOAInfoReportItem soaRep in reportItemList)
            {
                cus = new Customer();
                htmlReport.Append("<tr valign='middle' >");
                htmlReport.Append("<td align='middle'><b>" + soaRep.LegalEntity + "</b></td>");
                cus = CommonRep.GetQueryable<Customer>().
                    Where(c => c.CustomerNum == soaRep.CustomerNum).FirstOrDefault();
                htmlReport.Append("<td align='middle'><b>" + soaRep.CustomerNum + "</b></td>");
                htmlReport.Append("<td align='middle'><b>" + soaRep.CustomerName + "</b></td>");

                foreach (var curr in overdueCurrencys)
                {
                    var curAmt = soaRep.overdueCharge.Find(C => C.Item1 == curr.Key);
                    decimal amt = 0;
                    if (curAmt != null)
                    {
                        amt = curAmt.Item2;
                        overdueCurrencysTotal[curr.Key] += amt;
                    }
                    else
                    {
                        amt = 0;
                    }
                    htmlReport.Append("<td align='middle'><b>" + curr.Key + "</b></td>");
                    htmlReport.Append("<td align='right'><b>" + amt + "</b></td>");
                }
                foreach (var curr in currCurrencys)
                {
                    var curAmt = soaRep.currentCharge.Find(C => C.Item1 == curr.Key);
                    decimal amt = 0;
                    if (curAmt != null)
                    {
                        amt = curAmt.Item2;
                        currCurrencysTotal[curr.Key] += amt;
                    }
                    else
                    {
                        amt = 0;
                    }
                    htmlReport.Append("<td align='middle'><b>" + curr.Key + "</b></td>");
                    htmlReport.Append("<td align='right'><b>" + amt + "</b></td>");
                }
                htmlReport.Append("</tr>");
            }

            #endregion
            #region footer

            int col= colCountCurrent * 2+ colCountOverDue * 2;
            var overDueAndCurrent = overdueCurrencysTotal.Concat(currCurrencysTotal).GroupBy(g => g.Key).Select(t => new { key = t.Key, amt = t.Sum(p => p.Value) });
            foreach (var item in overDueAndCurrent)
            {
                htmlReport.Append("<tr valign='middle' align='right' >");
                htmlReport.Append("<th colspan='3'>Grand " + item.key+ " Total</th>");
                htmlReport.Append("<th  colspan=" + col + "><b>" + item.amt + "</b></th>");
                htmlReport.Append("</tr>");
            }
            //overDue+Current
            //foreach (var curr in overdueCurrencysTotal)
            //{
            //    htmlReport.Append("<td><b>" + curr.Key + "</b></td>");
            //    htmlReport.Append("<td><b>" + curr.Value + "</b></td>");
            //}

            //foreach (var curr in currCurrencysTotal)
            //{
            //    htmlReport.Append("<td><b>" + curr.Key + "</b></td>");
            //    htmlReport.Append("<td><b>" + curr.Value + "</b></td>");
            //}
            

            #endregion
            htmlReport.Append("</TABLE>");

            return htmlReport.ToString();
        }
    }
}
